package com.yayan.xh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.yayan.xh.bean.MatchBean;

public class MatchDao extends ConnectionBase implements IMatchDao{
	private Connection conn = null;
	
	private QueryRunner qRunner = null;
	
	private PreparedStatement ps = null;
	
	private ResultSet rsKey = null;
	
	@Override
	public List<Map<String, Object>> getMatchList(Map<String, String> map) throws Exception{
		List<Map<String, Object>> result = null;
		try {
			qRunner = new QueryRunner();
			//获取赛事信息
			String sql = "select m_id as matchId, m_name as name, m_detail as detail, "
					+ "m_address as address, m_longitude as longitude, m_latitude as latitude, "
					+ "m_stardatetime as stardate, m_enddatetime as enddate, "
					+ "m_status as status, m_text as text from tb_match where shop_id=? and m_status<=1 order by m_stardatetime desc ";
			conn = getConn();
			result = qRunner.query(conn, sql, new MapListHandler(), map.get("shopId"));
			for (int i = 0; i < result.size(); i++) {
				List<Map<String, Object>> imgResult = null;
				Map<String, Object> matchMap = result.get(i);
				//获取赛事图片信息
				String imgSql = "select im_url as imgUrl from tb_image where im_id=? and shop_id=?";
				imgResult = qRunner.query(conn, imgSql, new MapListHandler(),matchMap.get("matchId"), map.get("shopId"));
				matchMap.put("imgList", imgResult);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public List<Map<String, Object>> getMatchListByMatchId(Map<String, String> map) throws Exception{
		List<Map<String, Object>> result = null;
		try {
			qRunner = new QueryRunner();
			//获取赛事信息
			String sql = "select m_id as matchId, m_name as name, m_detail as detail, "
					+ "m_address as address, m_longitude as longitude, m_latitude as latitude, "
					+ "m_stardatetime as stardate, m_enddatetime as enddate, "
					+ "m_status as status, m_text as text from tb_match where m_id=? and shop_id=?";
			conn = getConn();
			result = qRunner.query(conn, sql, new MapListHandler(), map.get("matchId"), map.get("shopId"));
			for (int i = 0; i < result.size(); i++) {
				List<Map<String, Object>> imgResult = null;
				Map<String, Object> matchMap = result.get(i);
				//获取赛事图片信息
				String imgSql = "select im_url as imgUrl from tb_image where m_id=? and shop_id=?";
				imgResult = qRunner.query(conn, imgSql, new MapListHandler(), matchMap.get("matchId"), map.get("shopId"));
				matchMap.put("imgList", imgResult);
				
				List<Map<String, Object>> proResult = null;
				//获取赛事产品信息（门票）
				String proSql = "select p_id as proId, p_name as proName, p_type as proType, "
						+ "p_price as proPrice from tb_product where m_id=? and shop_id=?;";
				proResult = qRunner.query(conn, proSql, new MapListHandler(), matchMap.get("matchId"), map.get("shopId"));
				matchMap.put("proList", proResult);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}
	
	@Override
	public int saveMatch(Map<String, String> map) throws Exception{
		int id = 0;
		boolean f = false;
		try {
			String sql = "insert into tb_match(shop_id, m_name, m_detail, m_address, m_longitude, m_latitude, m_stardatetime, "
					+ "m_enddatetime, m_createdatetime, m_status, m_text) values(?,?,?,?,?,?,?,?,now(),0,?)";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, map.get("shopId"));
			ps.setObject(2, map.get("name"));
			ps.setObject(3, map.get("detail"));
			ps.setObject(4, map.get("address"));
			ps.setObject(5, map.get("longitude"));
			ps.setObject(6, map.get("latitude"));
			ps.setObject(7, map.get("starDate"));
			ps.setObject(8, map.get("endDate"));
			ps.setObject(9, map.get("text"));
			f = ps.executeUpdate() > 0 ? true : false;
			if(f) {
				rsKey = ps.getGeneratedKeys();
				rsKey.next();
				id=rsKey.getInt(1);
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return id;
	}

	@Override
	public List<MatchBean> getMatchListBack(Map<String, String> map) throws Exception {
		List<MatchBean> result = null;
		try {
			qRunner = new QueryRunner();
			//获取赛事信息
			String sql = "select * from tb_match where shop_id=? order by m_stardatetime desc ";
			conn = getConn();
			result = qRunner.query(conn, sql, new BeanListHandler<MatchBean>(MatchBean.class), map.get("shopId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public MatchBean getMatchByMatchIdBack(Map<String, String> map) throws Exception {
		MatchBean result = null;
		try {
			qRunner = new QueryRunner();
			//获取赛事信息
			String sql = "select * from tb_match where m_id=? and shop_id=?";
			conn = getConn();
			result = qRunner.query(conn, sql, new BeanHandler<MatchBean>(MatchBean.class), map.get("matchId"), map.get("shopId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public boolean modifyMatch(Map<String, String> map) throws Exception {
		boolean f = false;
		try {
			String sql = "update tb_match set m_name=?, m_detail=?, m_address=?, m_longitude=?, m_latitude=?, m_stardatetime=?, m_enddatetime=?, m_status=?, m_text=? where shop_id=? and m_id=?";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, map.get("name"));
			ps.setObject(2, map.get("detail"));
			ps.setObject(3, map.get("address"));
			ps.setObject(4, map.get("longitude"));
			ps.setObject(5, map.get("latitude"));
			ps.setObject(6, map.get("starDate"));
			ps.setObject(7, map.get("endDate"));
			ps.setObject(8, map.get("status"));
			ps.setObject(9, map.get("text"));
			ps.setObject(10, map.get("shopId"));
			ps.setObject(11, map.get("matchId"));
			f = ps.executeUpdate() > 0 ? true : false;
			
			String sproductSql = "update tb_product set p_price=? where shop_id=? and m_id=? and p_type=0";
			ps = conn.prepareStatement(sproductSql);
			ps.setObject(1, map.get("sproduct"));
			ps.setObject(2, map.get("shopId"));
			ps.setObject(3, map.get("matchId"));
			boolean sf = ps.executeUpdate() > 0 ? true : false;
			
			String eproductSql = "update tb_product set p_price=? where shop_id=? and m_id=? and p_type=1";
			ps = conn.prepareStatement(eproductSql);
			ps.setObject(1, map.get("eproduct"));
			ps.setObject(2, map.get("shopId"));
			ps.setObject(3, map.get("matchId"));
			boolean ef = ps.executeUpdate() > 0 ? true : false;
			
			if(f && sf && ef) {
				conn.commit();
				conn.setAutoCommit(true);
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return false;
	}

	
	
}
